{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Filtering & Sorting Data in SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Databases are commonly used for persostent data storage, and therefore it is common to add or remove rows as new data is created (e.g someone places an order) or destroyed (e.g a product is discontinued). This may be performed automatically via an application's database connection; we wil use database connections later in this notebook. However, in the mean time we wil load in a larger version of the first data set (Working with SQL) from file for analysis "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @testdb.sqlite'"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a database connection\n",
    "%sql sqlite:///testdb.sqlite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect('testdb.sqlite')\n",
    "customers = pd.read_csv('data/customers.csv')\n",
    "products = pd.read_csv('data/products.csv')\n",
    "orders = pd.read_csv('data/orders.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>21.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>18.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>24.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>29.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>20.12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  price\n",
       "0   0  21.78\n",
       "1   1  18.48\n",
       "2   2  24.17\n",
       "3   3  29.71\n",
       "4   4  20.12"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   id  price\n",
      "0   0  21.78\n",
      "1   1  18.48\n",
      "2   2  24.17\n",
      "3   3  29.71\n",
      "4   4  20.12\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>delivery_country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1866</td>\n",
       "      <td>686</td>\n",
       "      <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1921</td>\n",
       "      <td>617</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1921</td>\n",
       "      <td>54</td>\n",
       "      <td>Armenia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1921</td>\n",
       "      <td>566</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1921</td>\n",
       "      <td>128</td>\n",
       "      <td>Lebanon</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  customer_id  product_id delivery_country\n",
       "0   0         1866         686            India\n",
       "1   1         1921         617            China\n",
       "2   1         1921          54          Armenia\n",
       "3   1         1921         566            China\n",
       "4   1         1921         128          Lebanon"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### We will use pandas ***to_sql*** method to create table in our database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers.to_sql(\"customers\", conn, index=False, if_exists = 'replace')\n",
    "products.to_sql(\"products\", conn, index = False, if_exists = 'replace')\n",
    "orders.to_sql(\"orders\", conn, index=False, if_exists = 'replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite\n",
      "   sqlite:///testdb1.sqlite\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>product_id</th>\n",
       "        <th>delivery_country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>1866</td>\n",
       "        <td>686</td>\n",
       "        <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1921</td>\n",
       "        <td>617</td>\n",
       "        <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1921</td>\n",
       "        <td>54</td>\n",
       "        <td>Armenia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1921</td>\n",
       "        <td>566</td>\n",
       "        <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1921</td>\n",
       "        <td>128</td>\n",
       "        <td>Lebanon</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 1866, 686, 'India'),\n",
       " (1, 1921, 617, 'China'),\n",
       " (1, 1921, 54, 'Armenia'),\n",
       " (1, 1921, 566, 'China'),\n",
       " (1, 1921, 128, 'Lebanon')]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM orders\n",
    "    LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite\n",
      "   sqlite:///testdb1.sqlite\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>name</th>\n",
       "        <th>billing_country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Serenity Ladner</td>\n",
       "        <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Lucy Baldwin</td>\n",
       "        <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Skylar Coffman</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Riley Kinsey</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>Heidi Spurlock</td>\n",
       "        <td>Canada</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Serenity Ladner', 'Canada'),\n",
       " (2, 'Lucy Baldwin', 'Canada'),\n",
       " (5, 'Skylar Coffman', 'USA'),\n",
       " (6, 'Riley Kinsey', 'USA'),\n",
       " (7, 'Heidi Spurlock', 'Canada')]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM customers\n",
    "    LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite\n",
      "   sqlite:///testdb1.sqlite\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>21.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>18.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>24.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>29.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>20.12</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 21.78), (1, 18.48), (2, 24.17), (3, 29.71), (4, 20.12)]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM products\n",
    "    LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering and sorting data\n",
    "#### Filtering is principally accomplished using the $WHERE$ command"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite\n",
      "   sqlite:///testdb1.sqlite\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>delivery_country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>64</td>\n",
       "        <td>India</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 'India'), (60, 'India'), (60, 'India'), (60, 'India'), (64, 'India')]"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT id, delivery_country FROM orders\n",
    "WHERE delivery_country = 'India'\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Note the single equals sign *$=$* used in the conditional $WHERE$ keyword. This is different from python where double *==* is used.\n",
    "\n",
    "#### In SQL, we do not assign values to variables as in python. So there is no ambiguity or neccessity for a single equals sign to represnt assignment rather than eqaultiy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  *SQL  is  a  query  language*. \n",
    "#### It is used for performing trnasformation and aggregation of data present in databases. It is not meant for writing applications."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### We can perform other conditional filtering with symbols such as $  <,  >,  != , <=, >= $"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite\n",
      "   sqlite:///testdb1.sqlite\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>21.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>18.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>24.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>29.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>20.12</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 21.78), (1, 18.48), (2, 24.17), (3, 29.71), (4, 20.12)]"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM products\n",
    "WHERE price != 20\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite\n",
      "   sqlite:///testdb1.sqlite\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>21.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>24.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>29.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>20.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>27.55</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 21.78), (2, 24.17), (3, 29.71), (4, 20.12), (5, 27.55)]"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM products\n",
    "WHERE price > 20\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite\n",
      "   sqlite:///testdb1.sqlite\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>18.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>14.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>14.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>14.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>14.77</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 18.48), (6, 14.9), (12, 14.98), (13, 14.37), (14, 14.77)]"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM products \n",
    "WHERE price <= 20\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite\n",
      "   sqlite:///testdb1.sqlite\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>21.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>24.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>29.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>20.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>27.55</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 21.78), (2, 24.17), (3, 29.71), (4, 20.12), (5, 27.55)]"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM products\n",
    "WHERE price >= 20\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### We can additionally combine $WHERE$ with $LIKE$ for pattern matching an $IN$ for membership "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "####  For example we can find orders *Where the delivery_country begins with 'S'* using the $LIKE$ keyword with $WHERE$\n",
    "\n",
    "\n",
    "#### This is similiar in pandas to the $str$.$contains$ method\n",
    "\n",
    "#### A major difference between SQL $LIKE$ syntax and pandas $str$.$contains$ syntax is that pandas uses regular expression for pattern matching... \n",
    "\n",
    "#### *$Regular Expressions$ is a whole set of characters for indicating spaces, digits, and other characters and specifying the structure of various patterns in strings that we can search for*\n",
    "\n",
    "#### Different SQL falvours have thier own pattern matching syntax, so one might have to look up how to write thr pattern matching syntax for the sql falvour being used.... as that for MySql differs from MSQL or Postgre SQL..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite\n",
      "   sqlite:///testdb1.sqlite\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>delivery_country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Spain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>Senegal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Sweden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Sweden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>Sweden</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, 'Spain'), (11, 'Senegal'), (16, 'Sweden'), (16, 'Sweden'), (16, 'Sweden')]"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT id, delivery_country FROM orders\n",
    "WHERE delivery_country like 'S%'\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Note: The *%* sign in *like 'S%'* refers to any number of letters or combination following *'S'*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite\n",
      "   sqlite:///testdb1.sqlite\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>product_id</th>\n",
       "        <th>delivery_country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>211</td>\n",
       "        <td>200</td>\n",
       "        <td>858</td>\n",
       "        <td>Madagascar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>211</td>\n",
       "        <td>200</td>\n",
       "        <td>434</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>542</td>\n",
       "        <td>400</td>\n",
       "        <td>80</td>\n",
       "        <td>Sweden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>542</td>\n",
       "        <td>400</td>\n",
       "        <td>405</td>\n",
       "        <td>Sweden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>818</td>\n",
       "        <td>10</td>\n",
       "        <td>778</td>\n",
       "        <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>818</td>\n",
       "        <td>10</td>\n",
       "        <td>60</td>\n",
       "        <td>Canada</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(211, 200, 858, 'Madagascar'),\n",
       " (211, 200, 434, 'Mexico'),\n",
       " (542, 400, 80, 'Sweden'),\n",
       " (542, 400, 405, 'Sweden'),\n",
       " (818, 10, 778, 'Canada'),\n",
       " (818, 10, 60, 'Canada')]"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM orders\n",
    "WHERE customer_id IN (10, 200, 400);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Something all SQL falvours have in common is the $IN$ keyword for testing membership. \n",
    "\n",
    "#### This is similiar to *Pandas* $isin$ keyword, where we can check if a series or DataFrame  to see whether the values in a series are contained in some set of values.\n",
    "\n",
    "#### *Set of values here could be an actual set or a list or a tuple or any kind of list-like container of values*\n",
    "\n",
    "#### $Eg$\n",
    "#### s = pd.Series (['Lion', 'Cat', 'Dog', 'Eagle', 'cow']), name = 'animal')\n",
    "#### s.isin(['cow', 'lion])\n",
    "#### It would return boolean values for each rows whether the row values matches any of the search values\n",
    "#### 0 True\n",
    "#### 1 False\n",
    "#### 2 False\n",
    "#### 3 False\n",
    "#### 4 True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### We can also combine them with the usual logical operators: $AND$, $OR$, and $NOT$"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite\n",
      "   sqlite:///testdb1.sqlite\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>product_id</th>\n",
       "        <th>delivery_country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>211</td>\n",
       "        <td>200</td>\n",
       "        <td>434</td>\n",
       "        <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>542</td>\n",
       "        <td>400</td>\n",
       "        <td>80</td>\n",
       "        <td>Sweden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>542</td>\n",
       "        <td>400</td>\n",
       "        <td>405</td>\n",
       "        <td>Sweden</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(211, 200, 434, 'Mexico'),\n",
       " (542, 400, 80, 'Sweden'),\n",
       " (542, 400, 405, 'Sweden')]"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM orders\n",
    "WHERE customer_id IN (10, 200, 400)\n",
    "AND delivery_country NOT IN ('Madagascar', 'Canada');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite\n",
      "   sqlite:///testdb1.sqlite\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>31.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>7.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>33.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>41</td>\n",
       "        <td>8.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>44</td>\n",
       "        <td>5.8</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(10, 31.74), (17, 7.27), (22, 33.03), (41, 8.98), (44, 5.8)]"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM products\n",
    "WHERE price < 10 OR price > 30\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## $Sorting$"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### To sort our values, we can *ORDER BY* one or more values. We can also choose whether we sort in ascendng $(ASC)$ or descending $(DESC)$ order. \n",
    "\n",
    "#### *SQL sorts in ascending $(ASC)$* order by default similiar to the python convention"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite\n",
      "   sqlite:///testdb1.sqlite\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>product_id</th>\n",
       "        <th>delivery_country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>189</td>\n",
       "        <td>1</td>\n",
       "        <td>307</td>\n",
       "        <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>189</td>\n",
       "        <td>1</td>\n",
       "        <td>508</td>\n",
       "        <td>Poland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>949</td>\n",
       "        <td>1</td>\n",
       "        <td>431</td>\n",
       "        <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>949</td>\n",
       "        <td>1</td>\n",
       "        <td>592</td>\n",
       "        <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>146</td>\n",
       "        <td>5</td>\n",
       "        <td>263</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(189, 1, 307, 'Canada'),\n",
       " (189, 1, 508, 'Poland'),\n",
       " (949, 1, 431, 'Canada'),\n",
       " (949, 1, 592, 'Canada'),\n",
       " (146, 5, 263, 'USA')]"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM orders\n",
    "ORDER BY customer_id\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### We can $ORDER BY$ multiple columns and specify whether we want to ORDER in $(ASC)$ OR $(DESC)$ order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite\n",
      "   sqlite:///testdb1.sqlite\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>product_id</th>\n",
       "        <th>delivery_country</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>949</td>\n",
       "        <td>1</td>\n",
       "        <td>592</td>\n",
       "        <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>189</td>\n",
       "        <td>1</td>\n",
       "        <td>508</td>\n",
       "        <td>Poland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>949</td>\n",
       "        <td>1</td>\n",
       "        <td>431</td>\n",
       "        <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>189</td>\n",
       "        <td>1</td>\n",
       "        <td>307</td>\n",
       "        <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>146</td>\n",
       "        <td>5</td>\n",
       "        <td>864</td>\n",
       "        <td>USA</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(949, 1, 592, 'Canada'),\n",
       " (189, 1, 508, 'Poland'),\n",
       " (949, 1, 431, 'Canada'),\n",
       " (189, 1, 307, 'Canada'),\n",
       " (146, 5, 864, 'USA')]"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM orders\n",
    "ORDER BY customer_id ASC, product_id DESC\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.6.9 64-bit",
   "language": "python",
   "name": "python36964bit83539e9507bf4304ba502a0f8acf9217"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
